
DROP table jms_dm.dm_cn_departure_transfer_timely_route_analysis_dt;

CREATE TABLE jms_dm.dm_cn_departure_transfer_timely_route_analysis_dt (
  start_code varchar(50) comment '始发地 发件中心',
  order_source_code varchar(50) comment '订单来源code',
  nextstationcode varchar(200) comment '中心发件下一站',
  end_center_code varchar(200) comment '目的中心code',
  last_send_shipment_name varchar(200) comment '上一站装车任务单',
  send_shipment_name varchar(200) comment '本站发件任务单',
  start_name varchar(200) comment '始发地 发件中心',
  order_source_name varchar(200) comment '订单来源',
  center_type varchar(50) comment '中心类型,1始发2中转',
  start_agent_code varchar(50) comment '发件代理区',
  start_agent_name varchar(200) comment '发件代理区',
  send_account int comment '发件票数',
  route_lack_account int comment '静态路由缺失票数',
  transfer_intime_account int comment '转运及时票数',
  transfer_intime_rate double comment '出港转运及时率',
  without_arrival_account int comment '无到达时间票数',
  error_send_account int comment '错发票数',
  opp_delay_account int comment '操作延误票数',
  send_delay_account int comment '发车晚点票数',
  shipment_delay_account int comment '任务单问题票数',
  stop_delay_account int comment '经停点问题票数',
  all_line_transfer_intime_account int comment '全链路-转运及时票数',
  all_line_transfer_intime_rate double comment '全链路-转运及时率',
  shipment_error_account int comment '任务单错用票数',
  mage_region_code varchar(50) comment '管理大区编码',
  mage_region_name varchar(200) comment '管理大区名称',
  temporary_dispatch_account int comment '临时调度票量',
  reserve_route_account int comment '备用路由票数',
  epidemic_hoarding_account int comment '疫情囤货票数',
  store_count int comment '留仓票数',
  express_type_code varchar(50) comment '产品类型code',
  express_type_name varchar(200) comment '产品类型名称',
  reserved_seconds int comment '操作时长',
  start_provider_id varchar(50) comment '始发省份',
  start_provider_desc varchar(200) comment '始发省份',
  nextstation varchar(200) comment '中心发件下一站',
  nextstation_network_type varchar(200) comment '中心发件下一站网点类型',
  end_center_name varchar(200) comment '目的中心名称',
  date_time date COMMENT '数据日期'
) ENGINE=OLAP
DUPLICATE KEY(start_code, order_source_code, nextstationcode,end_center_code,last_send_shipment_name,send_shipment_name)
COMMENT "出港转运及时率汇总"
PARTITION BY RANGE(date_time)
(START ("2023-07-01") END ("2023-09-30") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(start_code,end_center_code) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-366",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2"
);

